#!/usr/bin/python
###################################################################################################                                                                                              
#                                                                                              
# DEVELOPERS : Luca Sanna                                                                        
# VERSIONE : 0.1                                                                                  
#                                                                                                                    
# E-MAIL: sanluca78@gmail.com                                                                                        
# COPYRIGHT: (c) 2015                                                                      
# PROGRAMMA: Py-Home-Slave  
#This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.                                                                          
###################################################################################################
import sqlite3 as lite
import sys,os

class Mydata():
    def __init__(self):
        self.db_real_time = '/var/tmp/real_time.db'
        db_is_new = not os.path.exists(self.db_real_time)
        conn = lite.connect(self.db_real_time)
        if db_is_new:
            conn.execute("create table if not exists real_time (Id INTEGER PRIMARY KEY, temp FLOAT, umid FLOAT, temp_d FLOAT,pulse FLOAT, rain INT);")
            conn.execute("create table if not exists pulse (Id INTEGER PRIMARY KEY, pulse1 INT,pulse2 INT,pulse3 INT,pulse4 INT,pulse5 INT,pulse6 INT,pulse7 INT,pulse8 INT);")
            conn.execute("insert into real_time (temp, umid, temp_d,pulse,rain) values (%f ,%f , %f, %f,%d)" %(0,0,0,0,0))
            conn.execute("insert into pulse (pulse1,pulse2,pulse3,pulse4,pulse5,pulse6,pulse7,pulse8) values (%d,%d,%d,%d,%d,%d,%d,%d)" %(0,0,0,0,0,0,0,0))
            conn.execute("create table if not exists tent (Id INTEGER PRIMARY KEY, open INT, close INT, half INT);")
            conn.execute("insert into tent (open, close, half) values (%d ,%d , %d)" %(0,0,0))
            b=0
            while b<6:
                conn.execute("insert into pulse (pulse1,pulse2,pulse3,pulse4,pulse5,pulse6,pulse7,pulse8) values (%d,%d,%d,%d,%d,%d,%d,%d)" %(0,0,0,0,0,0,0,0))
                b=b+1
        conn.commit()
        conn.close()

        self.db_configure = '/media/data/py-home/py-home-slave/py/db/configure.db'
        db_is_new = not os.path.exists(self.db_configure)
        conn = lite.connect(self.db_configure)
        if db_is_new:            
            conn.execute("create table if not exists configure (Id INTEGER PRIMARY KEY, label VARCHAR(150), power INT, man INT, sunrise INT, calendar INT, pulse INT, switch INT,button INT,enel INT,gas INT,acqua INT);")
            conn.execute("create table if not exists sunrise (Id INTEGER PRIMARY KEY, long FLOAT, lat FLOAT, timezone VARCHAR(150), duelamp INT, shifthour INT);")
            conn.execute("create table if not exists calib (Id INTEGER PRIMARY KEY, offsetemp FLOAT, offsetumid FLOAT, offsetdallas FLOAT);")
            conn.execute("create table if not exists sensor (Id INTEGER PRIMARY KEY, si INT, dallas INT,dallasid TEXT);")
            conn.execute("create table if not exists operation (Id INTEGER PRIMARY KEY, tent INT, mini INT, boiler INT,normal INT);")
            conn.execute("create table if not exists tent (Id INTEGER PRIMARY KEY, open INT, close INT,half INT,sec INT,imp INT, mode INT);")
            conn.execute("create table if not exists boiler (Id INTEGER PRIMARY KEY, temp_min FLOAT, temp_imp FLOAT, hour_cont INT, start_hour INT);")
            conn.execute("create table if not exists archive (Id INTEGER PRIMARY KEY, archive INT);")
            conn.execute("create table if not exists name (Id INTEGER PRIMARY KEY, nome VARCHAR(150));")
            conn.execute("insert into sunrise (long, lat, timezone,duelamp,shifthour) values ('%s' , '%s' , '%s' , %d , %d)" %('12.6500','45.9500','Europe/Rome',0,0))
            conn.execute("insert into calib (offsetemp, offsetumid, offsetdallas) values ('%f' , '%f', '%f')" %(0,0,0))
            conn.execute("insert into sensor (si, dallas,dallasid) values (%d , %d,'%s')" %(0,0,'000005a022dd'))
            conn.execute("insert into tent (open, close,half,sec,imp,mode) values (%d , %d,%d,%d,%d,%d)" %(20,20,10,5,20,0))
            conn.execute("insert into operation (tent, mini,boiler,normal) values (%d , %d,%d,%d)" %(0,0,0,0))
            conn.execute("insert into boiler (temp_min, temp_imp, hour_cont, start_hour) values ('%f' , '%f', %d, %d)" %(0,0,0,0))
            conn.execute("insert into archive (archive) values (%d)" %(0))
            conn.execute("insert into name (nome) values ('%s')" %('nome'))
            a=0
            while a<6:
                conn.execute("insert into configure (label, power,man,sunrise,calendar,pulse, switch,button,enel,gas,acqua) values ('%s',%d,%d,%d,%d,%d,%d,%d,%d,%d,%d)" %('label',0,0,0,0,0,0,0,0,0,0))
                a=a+1
        conn.commit()
        conn.close()
        command=("chmod 777 /media/data/py-home/py-home-slave/py/db/configure.db")
        os.system(command)

        self.db_storage = '/media/data/py-home/py-home-slave/py/db/storage.db'
        db_is_new = not os.path.exists(self.db_storage)
        conn = lite.connect(self.db_storage)
        if db_is_new:
            conn.execute("create table if not exists storage (Id INTEGER PRIMARY KEY, temp FLOAT, umid FLOAT, temp_d FLOAT, vento FLOAT, rain INT, enel FLOAT, gas FLOAT, acqua FLOAT, hour INT, day INT, month INT, year INT, lu INT);")
        conn.commit()
        conn.close()
        command=("chmod 777 /media/data/py-home/py-home-slave/py/db/storage.db")
        os.system(command)
        
        self.db_calendar = '/media/data/py-home/py-home-slave/luxcal410-calendar/db/mycal.cdb'

    def db_con_real(self):
        self.con_real_time = lite.connect(self.db_real_time)
        return self.con_real_time.cursor()
   
    def db_con_conf(self):
        self.con_configure = lite.connect(self.db_configure)
        return self.con_configure.cursor()
   
    def db_con_stor(self):
        self.con_storage = lite.connect(self.db_storage)
        return self.con_storage.cursor()
   
    def db_con_calendar(self):
        self.con_calendar = lite.connect(self.db_calendar)
        return self.con_calendar.cursor()
       
    def database_close(self):
        self.cur_real_time.close()
        self.cur_storage.close()
        self.cur_configure.close()
   
    def riavvio_db(self):
        self.database_close()
        self.database_conn()
       
    def save_real_time(self,temp,umid,pulse):
        cursor = self.db_con_real()
        cursor.execute("insert into real_time values (?,?,?,?)",(None, temp, umid,pulse))
        self.con_real_time.commit()
       
    def update_real_time(self,par,value):
        cursor = self.db_con_real()
        cursor.execute("update real_time set %s=%f where id = 1" %(par,value))
        self.con_real_time.commit()
        
    def update_pulse(self,par,value,id):
        cursor = self.db_con_real()
        cursor.execute("update pulse set %s=%f where id = %d" %(par,value,id))
        self.con_real_time.commit()
    
    def update_tent_real(self,par,value,id):
        cursor = self.db_con_real()
        cursor.execute("update tent set %s=%d where id = %d" %(par,value,id))
        self.con_real_time.commit()
       
    def view_real_time(self):
        cursor = self.db_con_real()
        cursor.execute("select * from real_time where id = 1")
        return cursor.fetchone()
        self.con_real_time.close()

    def save_storage(self,temp, umid, temp_d,vento,rain,enel,gas,acqua,hour,day,month,year,lu):
        cursor = self.db_con_stor()
        cursor.execute("insert into storage (temp,umid,temp_d,vento,rain,enel,gas,acqua, hour,day,month,year,lu) values (%f,%f,%f,%f,%d,%f,%f,%f,%d,%d,%d,%d,%d)" %(temp,umid,temp_d,vento, rain,enel,gas,acqua,hour,day,month,year,lu))
        self.con_storage.commit()
       
    def update_storage(self,par,value):
        self.con_storage.execute("update storage SET %s=%f where id = 1" %(par,value))
        self.con_storage.commit()
   
    def view_storage(self):
        self.con_storage.execute("select * from storage where id = 1")
        self.con_storage.commit()
        r=self.self.con_storage.store_result() #rende disponibili i risultati
        for x in r.fetch_row(0):
            return x
        self.con_storage.close()
#label, temp_imp, temp_min, cont_hour, coef, manual, calendar, sunrise, wifi, bt
 #   def save_configure(self,start_hour, start_min, stop_hour, stop_min, rele1, rele2, rele3, rele4, manual, calendar, sunrise):
  #      self.con_configure.execute("insert into configure values (?,?,?,?,?,?,?,?,?,?,?,?)" %(None, start_hour, start_min, stop_hour, stop_min, rele1, rele2, rele3, rele4, manual, calendar, sunrise))
   #     self.con_configure.commit()

    def update_configure(self,par,value,id):
        cursor=self.db_con_conf()
        cursor.execute("update configure SET %s=%f where id = %d" %(par,value,id))
        self.con_configure.commit()
        
        #self.con_configure.execute("update configure SET %s=%f where id = %d" %(par,value,id))
        #self.con_configure.commit()

    def view_configure(self):
        cursor = self.db_con_conf()
        cursor.execute("select * from configure")
        return cursor.fetchall()
        self.con_configure.close()

    def view_sunrise(self):
        cursor=self.db_con_conf()
        cursor.execute("select * from sunrise where id = 1")
        return cursor.fetchone()
        self.con_configure.close()

    def update_sunrise(self,par,value):
        cursor = self.db_con_conf()
        cursor.execute("update sunrise set %s=%f where id = 1" %(par,value))
        self.con_configure.commit()
   
    def view_calendar(self,sDate,eDate):
        cursor=self.db_con_calendar()
        cursor.execute("select * from events where sDate<='%s' and eDate>='%s'" %(sDate,eDate))
        return cursor.fetchall()
        self.con_calendar.close()
       
    def view_calib(self):
        cursor=self.db_con_conf()
        cursor.execute("select * from calib where id = 1")
        return cursor.fetchone()
        self.con_configure.close()
       
    def update_calib(self,par,value):
        cursor = self.db_con_conf()
        cursor.execute("update calib set %s=%f where id = 1" %(par,value))
        self.con_configure.commit()
        
    def view_sensor(self):
        cursor=self.db_con_conf()
        cursor.execute("select * from sensor where id = 1")
        return cursor.fetchone()
        self.con_configure.close()
        
    def view_archive(self):
        cursor=self.db_con_conf()
        cursor.execute("select * from archive where id = 1")
        return cursor.fetchone()
        self.con_configure.close()
        
    def view_tent(self):
        cursor=self.db_con_conf()
        cursor.execute("select * from tent where id = 1")
        return cursor.fetchone()
        self.con_configure.close()
        
    def update_tent(self,par,value):
        cursor = self.db_con_conf()
        cursor.execute("update tent set %s=%f where id = 1" %(par,value))
        self.con_configure.commit()

        
    def view_operation(self):
        cursor=self.db_con_conf()
        cursor.execute("select * from operation where id = 1")
        return cursor.fetchone()
        self.con_configure.close()
        
    def view_boiler(self):
        cursor=self.db_con_conf()
        cursor.execute("select * from boiler where id = 1")
        return cursor.fetchone()
        self.con_configure.close()
        
    def update_boiler(self,par,value):
        cursor = self.db_con_conf()
        cursor.execute("update boiler set %s=%f where id = 1" %(par,value))
        self.con_configure.commit()
        
    def view_name(self):
        cursor=self.db_con_conf()
        cursor.execute("select * from name where id = 1")
        return cursor.fetchone()
        self.con_configure.close()
        
    def update_name(self,par,value):
        cursor = self.db_con_conf()
        cursor.execute("update name set %s=%f where id = 1" %(par,value))
        self.con_configure.commit()

